package com.mililoan.persist.system.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.mililoan.common.bean.PageResultSet;
import com.mililoan.common.dao.AbstractBaseDao;
import com.mililoan.common.dao.JdbcPageResultSetDao;
import com.mililoan.common.utils.StackTraceUtil;
import com.mililoan.model.system.Department;
import com.mililoan.persist.system.DepartmentDao;

/**
 * 部门表DAO接口
 * @author taochen
 * @version 1.0
 * @since 2017-08-26
 */
@Repository("departmentDao")
public class DepartmentDaoImpl extends AbstractBaseDao<Department> implements DepartmentDao {
	private static final Logger LOGGER = LoggerFactory.getLogger(DepartmentDaoImpl.class);
	private static final String SELECT_SQL = "SELECT T.ID,T.DEP_CODE,T.DEP_NAME,T.MODULES,T.FLAG FROM SYS_DEPARTMENT T";
	
	public Long add(final Department department) throws Exception{
		JdbcTemplate template = this.getJdbcTemplate();
		Long newId = new Long(0);
		final String sql = "INSERT INTO SYS_DEPARTMENT(DEP_CODE,DEP_NAME,MODULES,FLAG" 					
			+ ") VALUES (?,?,?,?)";
		
		try {
			//返回主键
			KeyHolder keyHolder = new GeneratedKeyHolder();
			template.update(new  PreparedStatementCreator(){
				public PreparedStatement createPreparedStatement(Connection con) throws SQLException {					
					PreparedStatement ps = con.prepareStatement(sql, 
								PreparedStatement.RETURN_GENERATED_KEYS);
							ps.setInt(1, department.getDepCode());
							ps.setString(2, department.getDepName());
							ps.setString(3, department.getModules());
							ps.setString(4, department.getFlag());
					return ps;
				}
			},keyHolder);
			//获取主键
			newId = keyHolder.getKey().longValue();
		} catch (Exception e) {			
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return newId;	
	}
	
	public Long update(final Department department) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		String sql = "UPDATE SYS_DEPARTMENT T SET T.DEP_CODE=?,T.DEP_NAME=?,T.MODULES=?,T.FLAG=?"
				+ " WHERE T.ID=?";
		try {
			template.update(sql, new PreparedStatementSetter() {			
				public void setValues(PreparedStatement ps) throws SQLException {
						ps.setInt(1, department.getDepCode());
						ps.setString(2, department.getDepName());
						ps.setString(3, department.getModules());
						ps.setString(4, department.getFlag());
						ps.setLong(5, department.getId());
				}
			});
		}
		catch (DataAccessException e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);		
		}
		return department.getId();
	}
	
	public Long delete(Long keyId) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		try {
//			String sql = "DELETE FROM SYS_DEPARTMENT WHERE ID=?";
			String sql = "UPDATE SYS_DEPARTMENT T SET T.FLAG='0' WHERE T.ID=?";
			template.update(sql, new Object[] { keyId });
		} catch (DataAccessException e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return keyId;
	}
	
	@SuppressWarnings("unchecked")
	public PageResultSet<Department> queryDepartmentList(Department department, int page, int pageSize) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		ArrayList<Object> rag = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(SELECT_SQL + " WHERE 1=1");				
		if(!StringUtils.isEmpty(department.getDepName())){
			sql.append(" AND T.DEP_NAME = ?");
			rag.add(department.getDepName());
		}
		if(!StringUtils.isEmpty(department.getModules())){
			sql.append(" AND T.MODULES = ?");
			rag.add(department.getModules());
		}
		if(!StringUtils.isEmpty(department.getFlag())){
			sql.append(" AND T.FLAG = ?");
			rag.add(department.getFlag());
		}
		final JdbcPageResultSetDao<Department> pageDao = new JdbcPageResultSetDao<Department>(pageSize);
		pageDao.setPage(page);
		final List<Department> pagelist = pageDao.getPrs().getResult();
		PageResultSet<Department> l_pageResultSet = null;
		try {
			l_pageResultSet = pageDao.pageResultSet(sql.toString(), template, rag.toArray(), new RowCallbackHandler() {	
				public void processRow(ResultSet rs) throws SQLException {
					pagelist.add(buildDepartment(rs));
				}			
			});
		} catch (Exception e) {			
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return l_pageResultSet;
	}
	
	@SuppressWarnings({"unchecked", "rawtypes"})
	public Department getDepartmentById(Long keyId) throws Exception {
		JdbcTemplate template = this.getJdbcTemplate();
		Department department = new Department();
		String sql = new String(SELECT_SQL + " WHERE T.DEP_CODE = ?");
		
		try {
			department = (Department)template.queryForObject(sql, new Object[] { keyId },
					new RowMapper() {
						public Object mapRow(ResultSet rs, int rowNum) throws SQLException {						
						return buildDepartment(rs);
					}
				}
			);
		} catch (Exception e) {
			LOGGER.error(e.getMessage());
			throw new Exception(e);
		}
		return department;
	}
	
	private Department buildDepartment(ResultSet rs) {
	 	Department rsDepartment = new Department();
	 	try {
			rsDepartment.setId(rs.getLong("ID"));
			rsDepartment.setDepCode(rs.getInt("DEP_CODE"));
			rsDepartment.setDepName(rs.getString("DEP_NAME"));
			rsDepartment.setModules(rs.getString("MODULES"));
			rsDepartment.setFlag(rs.getString("FLAG"));
		} catch (Exception e) {
            LOGGER.error(StackTraceUtil.getStackTrace(e));
        }
		return rsDepartment;
	 }
}